﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Reflection;
using System.Data;
using Dapper;

namespace Pinger
{
	public static class DB
	{
		private static readonly string ConnStr;

		static DB()
		{
			ConnStr = InitConnString();
			var table = (string)ExecuteScalar("SELECT name FROM sqlite_master WHERE type='table' AND name=@name collate nocase", new DataDictionary { { "Name", "Hosts" } });
			if (table == null) // Init DB?
			{
				var createCount = ExecuteNonQuery("create table if not exists Hosts (ID integer primary key, Name text, Description text);");
				var insertCount = ExecuteNonQuery("insert into Hosts (Name,Description) values (@Name,@description)", new DataDictionary { { "Name", "127.0.0.1" }, { "Description", "localhost" } });
			}
		}

		private static string InitConnString()
		{
			var path = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
			var dbPath = Path.Combine(path, "Pinger.db");
			return "Data Source=" + dbPath;
		}

		public static int ExecuteNonQuery(string sql)
		{
			return ExecuteNonQuery(sql, null);
		}

		public static int ExecuteNonQuery(string sql, DataDictionary dict)
		{
			using (var conn = new System.Data.SQLite.SQLiteConnection(ConnStr))
			{
				conn.Open();
				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText = sql;
					cmd.CommandType = CommandType.Text;
					if (dict != null)
					{
						foreach (var pair in dict)
						{
							cmd.Parameters.AddWithValue(pair.Key, pair.Value);
						}
					}
					return cmd.ExecuteNonQuery();
				}
			}
		}

		public static object ExecuteScalar(string sql)
		{
			return ExecuteScalar(sql, null);
		}

		public static object ExecuteScalar(string sql, DataDictionary dict)
		{
			using (var conn = new System.Data.SQLite.SQLiteConnection(ConnStr))
			{
				conn.Open();
				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText = sql;
					cmd.CommandType = CommandType.Text;
					if (dict != null)
					{
						foreach (var pair in dict)
						{
							cmd.Parameters.AddWithValue(pair.Key, pair.Value);
						}
					}
					cmd.ExecuteNonQuery();
					return cmd.ExecuteScalar();
				}
			}
		}

		public static List<T> ExecuteQuery<T>(string sql)
		{
			return ExecuteQuery<T>(sql, null);
		}

		public static List<T> ExecuteQuery<T>(string sql, object param)
		{
			using (var conn = new System.Data.SQLite.SQLiteConnection(ConnStr))
			{
				conn.Open();
				var results = conn.Query<T>(sql, param, CommandType.Text);
				return results.ToList();
			}
		}

	}
}
